﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using JKF.Substructure;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using JKF.Entities.Model;

namespace JKF.DataModule.SqlServerDAL
{
   public partial  class dalPRODUCT:IDAL.IdalPRODUCT
    {
        // 定义要获取哪些字段 PRODUCT_KEY, SORT_KEY, NAME, MODEL, RETAIL_PRICE, PURCHASE_PRICE, TYPE, DESCRIPTION, SHOP_CODE, USER_CREATE, DATE_CREATE, DATE_UPDATE  
       private const string FieldShow = "PRODUCT_KEY, [dbo].[getProduct_SortNameByKey](SORT_KEY) AS SORT_KEY, NAME, MODEL, RETAIL_PRICE, PURCHASE_PRICE, TYPE, DESCRIPTION, SHOP_CODE, USER_CREATE, DATE_CREATE, DATE_UPDATE ";
        private const string FieldOrder = "NAME";
        private const string FieldKey = "PRODUCT_KEY";
        /// <summary>
        /// 获取产品列表
        /// </summary>
        /// <param name="TYPE">分类</param>
        /// <param name="NAME">产品名称</param>
        /// <param name="MODEL">型号/规格</param>
        /// <param name="pageCurrent">当前页码</param>
        /// <param name="pageSize">一页显示多少</param>
        /// <param name="pageCount">总页数</param>
        /// <param name="rowCount">总记录数</param>
        /// <returns>返回DataTable结果集</returns>
        public static DataTable GetProductsDAL(SYS_USERinfo userinfo, string TYPE, string NAME, string MODEL, int pageCurrent, int pageSize, ref int pageCount, ref int rowCount)
        {
            var sql = new StringBuilder();
            SYS_ROLEinfo roleInfo = new dalSYS_ROLE().GetModel(userinfo.ROLE_KEY);
            if (SysConfig.HeadShop.Contains(roleInfo.ROLE_NAME))
            {
                //总店获取产品列表，只能获取到自己创建的产品
                sql.AppendFormat(" SHOP_CODE IS NULL ");
            }
            else 
            {  //其他店铺只能看到自己创建和总店创建的产品。
                sql.AppendFormat(string.Format(@" shop_code is null or shop_code in(  select b.shop_code 
from   dbo.SYS_USER a
inner join sys_user_shop b on a.user_key=b.user_key
where b.user_key='{0}')", userinfo.USER_KEY.ToString()));
            }
            // 下面组装查询条件
            if (!string.IsNullOrEmpty(NAME))
            {
                sql.AppendFormat(" AND NAME  LIKE '%{0}%'", NAME);
            }
            if (!string.IsNullOrEmpty(TYPE))
            {
                sql.AppendFormat(" AND TYPE='{0}'", TYPE);
            }
            if (!string.IsNullOrEmpty(MODEL))
            {
                sql.AppendFormat(" AND MODEL  LIKE '%{0}%'", MODEL);
            }
            // 下面调用此方法去查询数据库
            return DataPage.GetPageInfo("dbo.PRODUCT", FieldKey, pageCurrent, pageSize, FieldShow, FieldOrder, sql.ToString(), ref pageCount, ref rowCount).Tables[0];

        }
       /// <summary>
       /// 执行Sql语句（包括插入Insert和更新Update）
       /// </summary>
       /// <param name="sql"></param>
       /// <returns></returns>
        public static bool ProductSQLDAL(string sql)
        {
           return DB.HR.ExecuteNonQuery(CommandType.Text, sql)>0;
        }
        /// <summary>
        /// 事务删除产品 
        /// </summary>
        /// <param name="shop_code">产品 ID</param>
        /// <returns></returns>
        public static string TransactionSQLDeleteDAL(Guid user_key,Guid product_key)
        {
            int result = -1;
            Database db = DB.HR;
            DbCommand dbCommand = db.GetStoredProcCommand("[dbo].[proc_ProductDelete]");
            db.AddInParameter(dbCommand, "@product_key", DbType.Guid, product_key);
            //db.AddInParameter(dbCommand, "@user_key", DbType.Guid, user_key);
            //db.AddOutParameter(dbCommand, "@result", DbType.Int32, result);
            return  db.ExecuteScalar(dbCommand).ToString();
            //return dbCommand.Parameters["@result"].Value.ToString();
        }
        /// <summary>
        /// 判断产品名称是否存在
        /// </summary>
        /// <param name="product_sort_name"></param>
        /// <returns></returns>
        public static bool ExistsProductNameDAL(string product_name)
        {
            object obj = DB.HR.ExecuteScalar(CommandType.Text, "select count(1) from dbo.PRODUCT where NAME='" + product_name + "'");
            return Convert.ToInt32(obj) > 0;
        }

        /// <summary>
        /// 构建产品分类与产品的树结构数据
        /// </summary> 
        /// <returns>返回DataTable结果集</returns>
        public static DataTable GetProduct_SortTreeDAL()
        {
            Database db = DB.HR;
            DbCommand dbCommand = db.GetStoredProcCommand("[dbo].[proc_Product_Sort_TOTree]");
            return db.ExecuteDataSet(dbCommand).Tables[0];
        }
    }
}
